Kevin-Blog
limit不能作为子查询，但是可以作为临时表

1. limit 分页

  • 该表的所有数据


  • 获取前4条数据 

select * from t1 limit 4;  


  • 从第2行开始（不包括2），获取4条数据

select * from t1 limit 2,4;


  • 获取4条数据，从第2行开始（不包括2）-> 和上面的功能是一样的只是写法不一样

select * from t1 limit 4 offset 2; 


2. limit 分页性能的优化 -> 记录当前页最大或最小的id -> 一般搭配着后台语言（Python，PHP）实现

  • 这里所使用到的 userinfo 表是索引章节中的表

  • 不建议使用 between 去做分页的优化，因为 id 有可能不是连续的（1,2,3,5,6,7,8,9,10,11），例如每一页有10数据，我要取第一页的数据此时 id 为 4 的这条数据被删除了，在不知情的情况下我们使用 between 1 and 10 获取数据那么只会获取到9条数据，而不是10条，如果真的要使用 between 去做分页优化的前提是 id 是连续不间断的

  • 如果直接使用 limit 在不做任何优化的情况下，页数越大速度越慢

# 比如现在有一百万条数据，每一页有10条数据，现在要取第100页的数据, MySQL会将前面的1000条数据查找（扫描）一遍找到第1000条数据，然后再从第1000条数据开始往后取10条数据

select * from userinfo limit 10000,10;

  • 上一页和下一页功能的分页优化

# 下一页的SQL语句 -> 记录当前页最后一条数据的id，然后查找大于该id的所有数据，然后再通过limit取该堆数据中的前10条数据 -> 因为MySQL是直接从该页的最后一条数据的开始查找数据，这样就不会像直接使用limit一样，需要从头开始扫描到指定的数据然后在进行获取该数据的后 num 位数据

# 比如该页最后一条数据的id是999990，然后查找id大于999990的数据，然后再通过limit取该堆数据中的前10条数据

select * from userinfo where id>999990 limit 10;



# 上一页的SQL语句 -> 记录当前页的第一条数据的id，然后查找小于该id的所有数据，然后进行倒序排序再通过limit取该堆数据中的前10条数据

# 比如该页第一条数据的id是999991，然后查找id小于999991的数据，然后进行倒序排序再通过limit取该堆数据中的前10条数据

select * from (select * from userinfo where id<999991 order by id desc limit 10) as A order by id asc;



  • 基于当前的页数往后跳几页的分页优化

    • 上一页 98 99 [100] 101 102 103 下一页

# 思路

# 计算所要跳转的页数和当前页数相差多少条数据，获取这堆相差的数据，然后进行倒序排序后使用limit获取这堆相差数据的前10条数据（前提是每一页数据为10条），而这前10条数据就是所要跳转页数的数据，最后对这10条数据进行正序排序

# 例如：当前页数是100，然后要获取103页数的数据，前提每一页数据为10条，因为103页数和当前页数相差30数据，所以首先要获取这相差的30条数据，然后再对这30条数据进行倒序排序后使用 limit 获取前10条，而这前10条就是103页数的数据，最后对这10条数据进行正序排序

# 写法一的固定写法

select * from 表名 where id>=(
  select B.id from (
    select A.id from (
      select id from userinfo where id > 当前页数最后一条数据的id limit 每一页数据的总数 * (需要跳转的页码 - 当前页码)
    ) as A order by A.id desc limit 每一页数据的总数
  ) as B order by B.id asc limit 1
) limit 每一页数据的总数;

# 写法一

select * from userinfo where id>=(
  select B.id from (
    select A.id from (
      select id from userinfo where id > 1000 limit 30
    ) as A order by A.id desc limit 10
  ) as B order by B.id asc limit 1
) limit 10;

# 写法二的固定写法

select * from 表名 where id in (
  select B.id from (
    select A.id from (
      select id from userinfo where id>当前页数最后一条数据的id limit 每一页数据的总数 * (需要跳转的页码 - 当前页码)
    ) as A order by A.id desc limit 每一页数据的总数
  ) as B
) order by id asc;

# 写法二

select * from userinfo where id in (
  select B.id from (
    select A.id from (
      select id from userinfo where id>1000 limit 30
    ) as A order by A.id desc limit 10
  ) as B
) order by id asc;

  • 基于当前的页数往前跳几页的分页优化

    • 上一页 98 99 100 101 102 [103] 下一页

# 计算所要跳转的页数和当前页数相差多少条数据，获取这堆相差的数据，然后进行倒序排序后使用limit获取这堆相差数据的前10条数据（前提是每一页数据为10条），而这前10条数据就是所要跳转页数的数据，最后对这10条数据进行正序排序

# 例如：当前页数是103，然后要获取100页数的数据，前提每一页数据为10条，因为100页数和当前页数相差30数据，所以首先要获取这相差的30条数据，然后再对这30条数据进行倒序排序后使用 limit 获取前10条，而这前10条就是100页数的数据，最后对这10条数据进行正序排序

# 写法一的固定写法

select * from 表名 where id>=(
  select A.id from (
    select id from userinfo where id < 当前页数第一条数据的id order by id desc limit 每一页数据的总数 * (当前页码 - 需要跳转的页码)
  ) as A order by A.id asc limit 1
) limit 每一页数据的总数;

# 写法一

select * from userinfo where id>=(
  select A.id from (
    select id from userinfo where id < 1021 order by id desc limit 30
  ) as A order by A.id asc limit 1
) limit 10;

# 写法二的固定写法

select * from 表名 where id in (
  select B.id from (
    select A.id from (
      select id from userinfo where id < 当前页数第一条数据的id order by id desc limit 每一页数据的总数 * (当前页码 - 需要跳转的页码)
    ) as A order by id asc limit 每一页数据的总数
  ) as B
);

# 写法二

select * from userinfo where id in (
  select B.id from (
    select A.id from (
      select id from userinfo where id<1021 order by id desc limit 30
    ) as A order by id asc limit 10
  ) as B
);


← order by 排序 →